Filtering (SELECT)

Filtering

When a database has a lot of data, it is necessary to filter the data so that the user can easily find the information he is looking for. The SQL statement for filtering is SELECT.

Tip

If a table has more than 100 items, the program must provide a set of filters (search textbox, check boxes, radio buttons, etc.) in order to reduce amount of data that the program has to fetch, and to help the user find what he is looking for.

Tip

Most computer programming languages has a command to perform a SELECT. Usually, the name of this command is ExecuteSelect and is used to retrieve data from a SQL database.

Tip

Most computer programming languages has the command ExecuteNonQuery to perform the SQL commands: INSERT, UPDATE or DELETE. ExecuteNonQuery returns an integer values indicating the number of rows affected.

GUI Filtering

The GUI controls (GUI elements) are used to build a text string that represents a SELECT statement with a set of WHERE restrictions. For instance, a textbox can be associated with a LIKE statement. In the same manner a drop down list (or a set of radio buttons) can be used to modify the WHERE part of a SELECT statement.

Tip

Google uses a textbox to create a SELECT-WHERE LIKE statement to filter the results when a user is performing a search over the Internet. In the same way, suppose you are creating an interface to find a student a Drop Down List can be used to filter the student by BA program (Electronics, Mechanical, Computer Systems,...).

Problem 1

Update your best_buy.sql file to create and populate the tables as shown below.

Problem 2

Update your best_buy.sql file to create the vw_item view to produce the output shown when executing SELECT * FROM vw_item. See Wintempla > SQL > Views .

Tip

Do not forget to store the primary key in a list view control, a drop down list, a list box in the Data field of the control, so that the program can access the primary key later on for another purposes. For instance, in the next example the drop down list for categories must include category_id in the Data field and descr in the Text of each item.

Problem 3

Create a C++ project called ByCategory to filter the items by category. Create a Wintempla Dialog Application project, and insert a list view control (lvItem), a drop down list (ddCategory) and a label as shown. Do not forget to edit the connection string in the stdafx.h file.

Step A

Double click the drop down list box and select the SelChange event in the tabs events as shown below.

Step B

Use the templates: SELECT list view and SELECT drop down list, to edit the ByCategory.cpp file as shown below.

ByCategory.h

#pragma once //______________________________________ ByCategory.h
#include "resource.h"
class ByCategory: public Win::Dialog
{
public:
     ByCategory()
     {
     }
     ~ByCategory()
     {
     }
     void UpdateItems();
...

ByCategory.cpp

...
void ByCategory::Window_Open(Win::Event& e)
{
     //________________________________________________ 1. lvItem: Column Setup
     lvItem.Cols.Add(0, LVCFMT_LEFT, 200, L"Item name");
     ...
     //________________________________________________ 2. ddCategory: Fill the drop down list
     Sql::SqlConnection conn;
     try
     {
          ...
     }
     catch (Sql::SqlException e)
     {
          ...
     }
     //_______________________________________________ 3. Select by default the first category
     ddCategory.SelectedIndex = 0;
     //_______________________________________________ 4. Display in list view
     UpdateItems();
}

void ByCategory::ddCategory_SelChange(Win::Event& e)
{
     UpdateItems();
}

void ByCategory::UpdateItems()
{
     //____________________________________________________________ 1. Get category_id from item selected
     LPARAM category_id;
     if (ddCategory.GetSelectedData(category_id) == false) return;
     //____________________________________________________________ 2. Create SELECT statement
     wstring sqlcmd;
     Sys::Format(sqlcmd,
          L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item WHERE category_id = %d",
          category_id);
     //____________________________________________________________ 3. Change cursor to Busy
     Win::HourGlassCursor hgc(true);
     //____________________________________________________________ 4. Execute SELECT
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          lvItem.SetRedraw(false); // stop redrawing the control when inserting items
          lvItem.Items.DeleteAll();
          conn.ExecuteSelect(sqlcmd, 100, lvItem);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     lvItem.SetRedraw(true);
}

Tip

The UpdateItems() function is created to update the items in the list view control based the category selection performed in the drop down list. This function is called when:

The program opens
The user changes the category using the drop down list

The UpdateItems() function begins by checking if one item is selected in the drop down list. Then, it extracts the category_id from the drop down list to dynamically create a SELECT statement. Finally, a SQL connection is open to retrieve data from the database and populate the list view control. Notice that before inserting data into the list view control, it is possible to stop updating the control by calling listview.SetRedraw(false); once the data is the list view control you must call listview.SetRedraw(true); to refresh in the screen the control.

Problem 4

Create a C# project called ByCategoryS to filter the items by category. Create a Windows Forms Application project, and insert a list view control (lvItem), a drop drop list box (a ComboBox called ddCategory) and a label as shown. Do not forget to edit the connection string in the DatabaseInfo class.

ListItem.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ByCategoryS
{
     public class ListItem
     {
          private int valueData;
          private string displayData;

          public ListItem(int valueData, string displayData)
          {
               this.valueData = valueData;
               this.displayData = displayData;
          }
          public string DisplayData
          {
               get
               {
                    return displayData;
               }
          }

          public int ValueData
          {
               get
               {
                    return valueData;
               }
          }
     }

}

DatabaseInfo.cs

using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ByCategoryS
{
     class DatabaseInfo
     {
          public static string GetConnectionInfo()
          {
               return "server=selo\\SQLExpress;database=best_buy;Trusted_Connection=yes";
          }
     }
}

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; // <<<<<<<<<<<<<< ADD THIS LINE

namespace ByCategoryS
{
     public partial class Form1 : Form
     {
          public Form1()
          {
               InitializeComponent();
          }

          private void Form1_Load(object sender, EventArgs e)
          {
               Text = "ByCategoryS";
               ddCategory.DropDownStyle = ComboBoxStyle.DropDownList;
               lvItem.View = View.Details;
               //
               lvItem.Columns.Add("Name", 150, HorizontalAlignment.Left);
               lvItem.Columns.Add("Model", 200, HorizontalAlignment.Left);
               lvItem.Columns.Add("Brand", 100, HorizontalAlignment.Left);
               lvItem.Columns.Add("Category", 100, HorizontalAlignment.Left);
               //
               SqlConnection conn = new SqlConnection(DatabaseInfo.GetConnectionInfo());
               SqlCommand cmd = null;
               SqlDataReader reader = null;
               ddCategory.DisplayMember = "displayData";
               ddCategory.ValueMember = "valueData";

               try
               {
                    conn.Open();
                    cmd = new SqlCommand("SELECT category_id, descr FROM category", conn);
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                    ddCategory.Items.Add(new ListItem(reader.GetInt32(0), reader["descr"].ToString()));
                    }
               }
               catch (SqlException ex)
               {
                    MessageBox.Show(ex.Message);
               }
               finally
               {
                    reader.Close();
                    conn.Close();
                    ddCategory.SelectedIndex = 0;
                    UpdateItems();
               }
          }

          // DO NOT add this line manually
          // Open the GUI editor, select the combo box (drop down list), and in
          // the Properties View click on Events (the yellow lighting) and search for
          // the SelectedIndexChanged event. Double click the event to add the function!
          private void ddCategory_SelectedIndexChanged(object sender, EventArgs e)
          {
               UpdateItems();
          }

          private void UpdateItems()
          {
               if (ddCategory.Items.Count == 0) return;
               int selectedIndex = (int)ddCategory.SelectedIndex;
               if (selectedIndex < 0) return;
               int category_id = ((ListItem)ddCategory.Items[selectedIndex]).ValueData;
               string sqlcmd = "SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item WHERE category_id = ";
               sqlcmd += category_id.ToString();
               SqlConnection conn = new SqlConnection(DatabaseInfo.GetConnectionInfo());
               SqlDataReader reader = null;
               SqlCommand cmd = null;
               ListViewItem lvi = null;
               string[] colText = new string[4];
               int item_id;
               try
               {
                    lvItem.Items.Clear();
                    conn.Open();
                    cmd = new SqlCommand(sqlcmd, conn);
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                    item_id = (int)reader["item_id"];
                    colText[0] = reader["item_descr"].ToString();
                    colText[1] = reader["model"].ToString();
                    colText[2] = reader["brand_descr"].ToString();
                    colText[3] = reader["category_descr"].ToString();
                    lvi = new ListViewItem(colText, 0, Color.Black, Color.White, this.Font);
                    lvi.Tag = item_id;
                    lvItem.Items.Add(lvi);
                    //ddCategory.Items.Add(new ListItem(reader.GetInt32(0), reader["descr"].ToString()));
                    }
               }
               catch (SqlException ex)
               {
                    MessageBox.Show(ex.Message);
               }
               finally
               {
                    reader.Close();
                    conn.Close();
               }
          }
     }
}

Problem 5

Create a C++ project called ByCategoryWeb to filter the items by category. Create a Wintempla Web Application project, and edit the connection string in the stdafx.h file.

Step A

Insert a drop drop list and set the properties (including the event) as shown.

Step B

Insert a list view control and set its name to lvData.

Step C

Edit the ByCategoryGUI.h file as shown.

Index.cpp

..
void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________ 1. lvData: Column Setup
     lvData.Cols.Add(LVCFMT_LEFT, 20, L"Item name");
     lvData.Cols.Add(LVCFMT_LEFT, 20, L"Model");
     lvData.Cols.Add(LVCFMT_LEFT, 20, L"Brand");
     lvData.Cols.Add(LVCFMT_LEFT, 20, L"Category");
     //________________________________________________ 2. ddCategory: Fill the drop down list
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(NULL, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT category_id, descr FROM category", 100, ddCategory);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
          return;
     }
     //_______________________________________________ 3. Select by default the first category
     if (h.FirstTime == true)
     {
          ddCategory.SelectedIndex = 0;
          //_____________________________________________ 4. Display in list view
          UpdateItems();
     }
}

void Index::ddCategory_onchange(Web::HttpConnector& h)
{
     UpdateItems();
}

void Index::UpdateItems()
{
     //____________________________________________________________ 1. Get category_id
     LPARAM category_id;
     if (ddCategory.GetSelectedData(category_id) == false) return;
     //____________________________________________________________ 2. Create SELECT statement
     wstring sqlcmd;
     Sys::Format(sqlcmd,
          L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item WHERE category_id = %d",
          category_id);
     Sql::SqlConnection conn;
     //____________________________________________________________ 3. Execute SELECT
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(NULL, CONNECTION_STRING);
          lvData.Items.DeleteAll();
          conn.ExecuteSelect(sqlcmd, 100, lvData);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Step E

Review the Index.js file. You may any custom client Javascript programming to this file. In this case, Wintempla calls the SyncAll function to update the data of the list view control.

Index.js

//_____________________________________________ Index.js
//window.onload=Window_onload();
//
//function Window_onload()
//{
//}

function ddCategory_onchange(basicUrl, controlID, eventID)
{
SyncAll(basicUrl, controlID, eventID);
}

Problem 6

Publish the web application called ByCategoryWeb to a web server, see Wintempla > Publishing a Web Site use Anonymous Access.

Publique la aplicación llamada ByCategoryWeb en un servidor web, vea Wintempla > Publishing a Web Site use Acceso Anónimo.

Problem 7

Create a C++ Wintempla project called ByBrand to filter the items by brand.

Problem 8

Create a C++ Wintempla Web Application project called ByBrandWeb to filter the items by brand. Use the green cascade style sheet by making double click in any of the lines of the head in the HTML view. Publish the web application to a web server using Anonymous Access.

Problem 9

Create a C++ Wintempla project called ByBoth to filter the items by category and by brand. When updating the items, by sure to have both: a valid category_id and a valid brand_id.

Problem 10

Create a C++ Wintempla Web Application project called ByBothWeb to filter the items by category and by brand. Publish the web application to a web server using Anonymous Access.

A Check Box List

The list view control can be used as a check box list. In this case, the list view control has two images to represent a check box. The list view control requires AT LEAST one column to display data and properly work. Because a check box list is a list view control, all functions and properties of the list view control are the same as the functions and properties of a check box list control. In other words, a check box list control and a list view control are the same. Therefore, to add columns and items to a check box list is the same than to add columns and items to a list view control. The code shown below shows how to check and unchecked an item.

Program.cpp

int item_index = 0;
//_____________________________________ Wintempla
lvClient.Items[item_index].Checked = true;
bool isChecked = lvClient.Items[item_index].Checked;
//_____________________________________ C#.NET
checkBoxClient.SetItemChecked(item_index, true);
bool isChecked = checkBoxClient.GetItemChecked(item_index);

Problem 11

Create a C++ Wintempla project called ByMulBrand to filter the items by several brands simultaneously (use a check box list to select the brands). The BuildSqlQuery function is used to build the SQL query based on which checkboxes are checked. The function returns true, if at least one checkbox is checked. When the first brand_id is added the main part of the SQL query is build. After that, the function appends a filter to the query using a SQL OR command.

ByMulBrand.h

#pragma once //______________________________________ ByMulBrand.h
#include "resource.h"

class ByMulBrand: public Win::Dialog
{
public:
     ByMulBrand()
     {
     }
     ~ByMulBrand()
     {
     }
     void UpdateItems();
     bool BuildSqlQuery(wstring& sqlcmd);
protected:
     ...
};

ByMulBrand.cpp

...

void ByMulBrand::Window_Open(Win::Event& e)
{
     //________________________________________________1. lvItem: Column Setup
     lvItem.Cols.Add(0, LVCFMT_LEFT, 180, L"Item name");
     lvItem.Cols.Add(1, LVCFMT_LEFT, 110, L"Model");
     lvItem.Cols.Add(2, LVCFMT_LEFT, 110, L"Brand");
     lvItem.Cols.Add(3, LVCFMT_LEFT, 110, L"Category");
     //_______________________________________________ 2. clBrand: Column Setup
     clBrand.Cols.Add(0, LVCFMT_LEFT, 120, L"Brand");
     //_______________________________________________ 3. clBrand: Fill the check box list
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT brand_id, descr FROM brand", 100, clBrand);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
          return;
     }
     //_______________________________________________ 4. Select by default the first brand
     clBrand.Items[0].Checked = true;
     //_______________________________________________ 5. Display in list view
     UpdateItems();
}

void ByMulBrand::clBrand_ItemChanged(Win::Event& e)
{
     UpdateItems();
}

bool ByMulBrand::BuildSqlQuery(wstring& sqlcmd)
{
     const int itemCount = clBrand.Items.Count;
     int count = 0;
     int i;
     //________________________________________________________ 1. Count how many are checked
     for(i = 0; i < itemCount; i++)
     {
          if (clBrand.Items[i].Checked == true) count++;
     }
     //________________________________________________________ 2. Create the SELECT statement
     if (count == 0)
     {
          return false;
     }
     else
     {
          wstring text;
          for(i = 0; i < itemCount; i++)
          {
               if (clBrand.Items[i].Checked == true)
               {
                    if (sqlcmd.empty() == true)
                    {
                         Sys::Format(sqlcmd,
                              L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item WHERE brand_id = %d",
                              clBrand.Items[i].Data);
                    }
                    else
                    {
                         Sys::Format(text, L" OR brand_id = %d", clBrand.Items[i].Data);
                         sqlcmd += text;
                    }
               }
          }
     }
     return true;
}

void ByMulBrand::UpdateItems()
{
     //______________________________________________________ 1. Change cursor to Busy
     Win::HourGlassCursor hgc(true);
     //______________________________________________________ 2. Create the SELECT statement
     wstring sqlcmd;
     if (BuildSqlQuery(sqlcmd) == false)
     {
          lvItem.Items.DeleteAll();
          return;
     }
     //______________________________________________________ 3. Execute SELECT
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          lvItem.SetRedraw(false); // stop redrawing the control when inserting items
          lvItem.Items.DeleteAll();
          conn.ExecuteSelect(sqlcmd, 100, lvItem);
          lvItem.SetRedraw(true);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Problem 12

Create a C++ Wintempla Web Application project called ByMulBrandWeb to filter the items by several brands simultaneously). BE AWARE that the Data value for each item in a checkbox list is a wstring variable. Publish the web application to a web server using Anonymous Access.

Index.h

#pragma once //_____________________________________________ Index.h
#include "resource.h"

class Index: public Web::Page
{
public:
     Index()
     {
     }
     ~Index()
     {
     }
     void UpdateItems();
     bool BuildSqlQuery(wstring& sqlcmd);
private:
     ...
};

Index.cpp

...
void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________ 1. lvItem: Column Setup
     ...
     //________________________________________________ 2. clBrand: Column Setup
     ...
     //________________________________________________ 3. clBrand: Fill the check box list
     Sql::SqlConnection conn;
     try
     {
          ...
     }
     catch (Sql::SqlException e)
     {
          ...
     }
     //_______________________________________________ 4. Select by default the first brand
     if (h.FirstTime == true)
     {
          ...
     }
}

void Index::clBrand_onchange(Web::HttpConnector& h)
{
     ...
}

bool Index::BuildSqlQuery(wstring& sqlcmd)
{
     const int itemCount = clBrand.Items.Count;
     int count = 0;
     int i;
     //_________________________________________________________________ 1. Count how many are checked
     ...
     //_________________________________________________________________ 2. Create SELECT statement
     if (count == 0)
     ...

     Sys::Format(sqlcmd, L"... %s", lvItem[i].Data.c_str());
     // Sys::Format(sqlcmd, L"... %d", stoi(lvItem[i].Data));
}

void Index::UpdateItems()
{
     //_________________________________________________________________ 1. Create SELECT statement
     wstring sqlcmd;
     if (BuildSqlQuery(sqlcmd) == false)
     {
          lvItem.Items.DeleteAll();
          return;
     }
     //_________________________________________________________________ 2. Execute SELECT
     Sql::SqlConnection conn;
     try
     {
          ...
     }
     catch (Sql::SqlException e)
     {
          ...
     }
}

Problem 13

Create a C# project called ByBothS to filter the items by category and by brand.

Tag

In C# some items in a control have the Tag property to store custom information. In database application, it is a good idea to store the primary key. In the next problem, each item in the list view control (in this case a check box list) has a Tag property; its value can be used to build the SQL query. The Tag property is equivalent in C# of the Data property in C++.

Problem 14

Create a C# project called ByMulBrandS to filter the items by several brands simultaneously. Insert two List View controls: one List View with the property of CheckBoxes set to true (for the brands) and the other List View control to display the items. Note that you can use a Check Box list control; however, in this problem we will use a List View control.

Form1.cs

...
using System.Data.SqlClient;

namespace ByMulBrandS
{
     public partial class Form1 : Form
     {
          ...

          private void Form1_Load(object sender, EventArgs e)
          {
               this.Text = "ByMulBrandS";
               clBrand.CheckBoxes = true;
               clBrand.Columns.Add("Dummy", 150, HorizontalAlignment.Left);
               clBrand.HeaderStyle = ColumnHeaderStyle.None;
               ...
               finally
               {
                    reader.Close();
                    conn.Close();
                    UpdateItems();
               }
          }

          private string BuildSqlQuery()
          {
               string sqlcmd = null;
               int itemCount = clBrand.Items.Count;
               int count = 0;
               int i;
               //________________________________ count how many are checked
               ...
               //________________________________ Build the SQL query
               if (count == 0)
               {
                    return null;
               }
               else
               {
                    ...
                    for(...)
                    {
                         if (clBrand.Items[i].Checked == true)
                         {
                              if (sqlcmd == null)
                              {
                              }
                              else
                              {
                                   sqlcmd += clBrand.Items[i].Tag.ToString();
                              }
                         }
                    }
               }
               return sqlcmd;
          }

          private void UpdateItems()
          {
               string sqlcmd = BuildSqlQuery();
               if (sqlcmd == null)
               {
                    lvItem.Items.Clear();
                    return;
               }

               ...
          }

          private void clBrand_ItemChecked(object sender, ItemCheckedEventArgs e)
          {
               UpdateItems();
          }
     }
}

Problem 15

Convert the ByBoth project to a Dual application. In this case, you will to edit the web page after using Merge with Desktop Application. Remember to open the ByBoth.cpp using Wintempla to complete the merging. Basically, Wintempla will create a layout table with two rows and four columns. You need to delete the extra three columns from the second row of the layout table, and instead use the colspan command to extend the list view control to the four columns of the layout table as shown below. Do not forget to edit the connection string in the stdafx.h file of both projects.

ByBoth.cpp

...
void ByBoth::Window_Open(Win::Event& e)
{
     //________________________________________________ 1. lvItem: Column Setup
     lvItem.Cols.Add(0, LVCFMT_LEFT, 180, L"Item name");
     lvItem.Cols.Add(1, LVCFMT_LEFT, 110, L"Model");
     lvItem.Cols.Add(2, LVCFMT_LEFT, 110, L"Brand");
     lvItem.Cols.Add(3, LVCFMT_LEFT, 110, L"Category");
     //________________________________________________ 2. Call Dual Window_Open
     ByBothDual::Window_Open(*this, NULL);
     //_______________________________________________ 3. Select by default the first brand and first category
     ddBrand.SelectedIndex = 0;
     ddCategory.SelectedIndex = 0;
     //_______________________________________________ 4. Display en list view
     ByBothDual::UpdateItems(*this, NULL);
}

void ByBoth::ddCategory_SelChange(Win::Event& e)
{
     ByBothDual::UpdateItems(*this, NULL);
}

void ByBoth::ddBrand_SelChange(Win::Event& e)
{
     ByBothDual::UpdateItems(*this, NULL);
}

Step A

Edit the Index Web page using Wintempla to add the onchange event from both drop down lists.

Index.cpp

..
void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________ 1. lvItem: Column Setup
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Item name");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Model");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Brand");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Category");
     //________________________________________________ 2. Call Dual Window_Open
     ByBothDual::Window_Open(*this, &h);
     //_______________________________________________ 3. Select by default the first brand and first category
     if (h.FirstTime == true) ddCategory.SelectedIndex = 0;
     if (h.FirstTime == true) ddBrand.SelectedIndex = 0;
     //_______________________________________________ 4. Display en list view
     ByBothDual::UpdateItems(*this, &h);
}

void Index::ddCategory_onchange(Web::HttpConnector& h)
{
     ByBothDual::UpdateItems(*this, &h);
}

void Index::ddBrand_onchange(Web::HttpConnector& h)
{
     ByBothDual::UpdateItems(*this, &h);
}

ByBothDual.h

#pragma once //_____________________________________________ ByBothDual.h

class ByBothDual
{
public:
     ByBothDual()
     {
          Init();
     }
     ~ByBothDual()
     {
     }
     void UpdateItems(Sys::IWindow& window, Web::HttpConnector* h);
     ...
};

ByBothDual.cpp

...
void ByBothDual::Window_Open(Sys::IWindow& window, Web::HttpConnector* h)
{
     //________________________________________________ 2. ddCategory and ddBrand: Fill the drop down lists
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(window, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT category_id, descr FROM category", 100, ddCategoryD);
          conn.ExecuteSelect(L"SELECT brand_id, descr FROM brand", 100, ddBrandD);
     }
     catch (Sql::SqlException e)
     {
          window.MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

void ByBothDual::UpdateItems(Sys::IWindow& window, Web::HttpConnector* h)
{
     //____________________________________________________________ 1. Get category_id
     LPARAM category_id;
     if (ddCategoryD.GetSelectedData(category_id) == false) return;
     //____________________________________________________________ 2. Get brand_id
     LPARAM brand_id;
     if (ddBrandD.GetSelectedData(brand_id) == false) return;
     //____________________________________________________________ 3. Create SELECT statement
     wstring sqlcmd;
     Sys::Format(sqlcmd,
          L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item WHERE category_id = %d AND brand_id = %d",
          category_id, brand_id);
     //____________________________________________________________ 4. Execute SELECT
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(window, CONNECTION_STRING);
          conn.ExecuteSelect(sqlcmd, 100, lvItemD);
     }
     catch (Sql::SqlException e)
     {
          window.MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Problem 16

Create a Wintempla Dialog application called RawSelect to store data from a SQL database in a custom data structure.

stdafx.h

...
#define CONNECTION_STRING L"DRIVER={SQL Server};server=localhost\\SQLEXPRESS;database=best_buy;Trusted_Connection=yes"

struct Client
{
     int client_id;
     wchar_t first_name[64];
     wchar_t last_name[64];
     Sys::Time birthdate;
};

RawSelect.cpp

...
void RawSelect::Window_Open(Win::Event& e)
{
     Sql::SqlConnection conn;
     wstring cmd;
     vector<Client> client_list;
     Client client;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT client_id, first_name, last_name, birthdate FROM client");
          conn.BindColumn(1, client.client_id);
          conn.BindColumn(2, client.first_name, 64);
          conn.BindColumn(3, client.last_name, 64);
          conn.BindColumn(4, client.birthdate);
          while (conn.Fetch() == true)
          {
               client_list.push_back(client);
          }
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Filtering (SELECT)

Filtering

GUI Filtering

A Check Box List

Tag

© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home